/**
 * 
 */
package org.shark.jdbcTemplateTool.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import javax.persistence.Version;

import org.apache.commons.lang3.StringUtils;
import org.crazycake.utils.CamelNameUtils;
import org.shark.jdbcTemplateTool.exception.NoColumnAnnotationFoundException;
import org.shark.jdbcTemplateTool.exception.NoDefinedGetterException;
import org.shark.jdbcTemplateTool.exception.NoIdAnnotationFoundException;
import org.shark.jdbcTemplateTool.model.SqlParamsPairs;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author lenovo
 * @description
 * @date 2016年8月15日
 */
public class ModelSqlUtils {

	private static Logger logger = LoggerFactory.getLogger(ModelSqlUtils.class);

	private static Map<Class<?>, Table> mapTableAnno = new ConcurrentHashMap<Class<?>, Table>();

	private static Map<Class<?>, Map<String, Method>> mapClassMethod = new ConcurrentHashMap<Class<?>, Map<String, Method>>();

	private static Map<Method, String> mapMethodColumn = new ConcurrentHashMap<Method, String>();

	private static Map<Method, Boolean> mapMethodTranAnno = new ConcurrentHashMap<Method, Boolean>();

	/**
	 * 从po对象中分析出insert语句
	 * 
	 * @param po
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 */
	public static <T> SqlParamsPairs getInsertFromObject(T po) throws Exception {

		// 用来存放insert语句
		StringBuffer insertSql = new StringBuffer();
		// 用来存放?号的语句
		StringBuffer paramsSql = new StringBuffer();

		// 用来存放参数值
		List<Object> params = new ArrayList<Object>();

		// 分析表名
		String tableName = getTableName(po.getClass());

		insertSql.append("insert into " + tableName + " (");

		// 计数器
		int count = 0;

		// 分析列
		Field[] fields = po.getClass().getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field f = fields[i];

			if ("serialVersionUID".equals(f.getName())) {
				continue;
			}

			// 获取具体参数值
			Method getter = getGetter(po.getClass(), f);

			if (getter == null) {
				continue;
			}

			Object value = getter.invoke(po);
			if (value == null) {
				// 如果参数值是null并且是ID列, 就直接跳过（不允许覆盖为null值，规范要求更新的每个字段都要有值，没有值就是空字符串）
				Id id = getter.getAnnotation(Id.class);
				if (id != null) {
					continue;
				}
			}

			Boolean hasTranAnno = null;
			if (mapMethodTranAnno.containsKey(getter)) {
				hasTranAnno = mapMethodTranAnno.get(getter);
			} else {
				Transient tranAnno = getter.getAnnotation(Transient.class);
				hasTranAnno = tranAnno != null;
				mapMethodTranAnno.put(getter, hasTranAnno);
			}
			if (hasTranAnno.booleanValue()) {
				// 如果有 Transient 标记直接跳过
				continue;
			}

			// 获取字段名
			String columnName = getColumnNameFromGetter(getter, f);

			if (count != 0) {
				insertSql.append(",");
			}
			insertSql.append(columnName);

			if (count != 0) {
				paramsSql.append(",");
			}

			if (value != null) {
				paramsSql.append("?");

				params.add(value);
			} else {
				paramsSql.append("null");
			}
			count++;
		}

		String insertFieldSql = insertSql.toString() + ")";
		String insertvalue = "(" + paramsSql + ")";

		insertSql.append(") values (");
		insertSql.append(paramsSql + ")");


		SqlParamsPairs sqlAndParams = new SqlParamsPairs(insertSql.toString(), params.toArray());
		sqlAndParams.setInsertField(insertFieldSql);
		sqlAndParams.setInsertValue(insertvalue);
		logger.debug(sqlAndParams.toString());

		return sqlAndParams;

	}

	/**
	 * 获取属性的getter方法
	 * 
	 * @param clazz
	 * @param f
	 * @return
	 */
	private static <T> Method getGetter(Class<T> clazz, Field f) {
		String getterName = "get" + CamelNameUtils.capitalize(f.getName());

		Map<String, Method> mapMethod = mapClassMethod.get(clazz);
		if (mapMethod == null) {
			mapMethod = new ConcurrentHashMap<String, Method>();
			mapClassMethod.put(clazz, mapMethod);
		}

		Method getter = mapMethod.get(getterName);
		if (getter == null) {
			try {
				getter = clazz.getMethod(getterName);
			} catch (Exception e) {
				logger.debug(getterName + " doesn't exist!", e);
			}
			mapMethod.put(getterName, getter);
		}
		return getter;
	}

	/**
	 * 从po类获取表名
	 * 
	 * @param po
	 * @return
	 */
	private static <T> String getTableName(Class<T> clazz) {

		Table tableAnno = null;
		if (mapTableAnno.containsKey(clazz)) {
			tableAnno = mapTableAnno.get(clazz);
		} else {
			tableAnno = clazz.getAnnotation(Table.class);
			if (tableAnno != null) {
				mapTableAnno.put(clazz, tableAnno);
			}
		}
		if (tableAnno != null) {
			if (tableAnno.catalog() != null) {
				return tableAnno.catalog() + "." + tableAnno.name();
			}
			return tableAnno.name();
		}
		// if Table annotation is null
		String className = clazz.getName();
		return CamelNameUtils.camel2underscore(className.substring(className.lastIndexOf(".") + 1));
	}/**
	 * 从对象中获取update语句
	 * 
	 * @param po
	 * @return
	 * @throws Exception
	 */
	public static SqlParamsPairs getUpdateFromObject(Object po) throws Exception {
		return getUpdateFromObjectBase(po, true);
	}

	/**
	 * 从对象中获取update语句
	 * 
	 * @param po
	 * @return
	 * @throws Exception
	 */
	public static SqlParamsPairs getUpdateFromObjectNotNull(Object po) throws Exception {
		return getUpdateFromObjectBase(po, false);
	}
	


	/**
	 * 从对象中获取update语句
	 * 
	 * @param po
	 * @return
	 * @throws Exception
	 */
	public static SqlParamsPairs getUpdateFromObjectBase(Object po, boolean hasNull) throws Exception {

		// 用来存放insert语句
		StringBuffer updateSql = new StringBuffer();

		// 用来存放where语句
		StringBuffer whereSql = new StringBuffer();

		// 用来存放参数值
		List<Object> params = new ArrayList<Object>();

		// 用来存储id
		Object idValue = null;

		// 用来存储Version 字段
		Object versionValue = null;

		// 分析表名
		String tableName = getTableName(po.getClass());

		updateSql.append("update " + tableName + " set");

		// 分析列
		Field[] fields = po.getClass().getDeclaredFields();

		// 用于计数
		int count = 0;
		for (int i = 0; i < fields.length; i++) {
			Field f = fields[i];

			// 获取具体参数值
			Method getter = getGetter(po.getClass(), f);

			if (getter == null) {
				continue;
			}

			Transient tranAnno = getter.getAnnotation(Transient.class);
			if (tranAnno != null) {
				// 如果有 Transient 标记直接跳过
				continue;
			}

			Object value = getter.invoke(po);
			if (value == null && !hasNull) {
				// 如果参数值是null就直接跳过（不允许覆盖为null值，规范要求更新的每个字段都要有值，没有值就是空字符串）
				continue;
			}

			// 获取字段名
			String columnName = getColumnNameFromGetter(getter, f);

			// 看看是不是主键
			Id idAnno = getter.getAnnotation(Id.class);
			if (idAnno != null) {
				if (!StringUtils.isBlank(whereSql.toString())) {
					whereSql.append(" and ");
				}
				// 如果是主键
				whereSql.append(columnName + " = ?");
				idValue = value;
				continue;
			}

			// 看看是不是Version字段
			Version versionAnno = getter.getAnnotation(Version.class);
			if (versionAnno != null) {
				if (!StringUtils.isBlank(whereSql.toString())) {
					whereSql.append(" and ");
				}
				whereSql.append(columnName + " = ?");
				versionValue = value;

				if (count != 0) {
					updateSql.append(",");
				}
				updateSql.append(" " + columnName + " = " + columnName + " + 1");
				continue;
			}

			// 如果是普通列
			if (hasNull && value == null) {
				if (count != 0) {
    				updateSql.append(",");
    			}
    			updateSql.append(" " + columnName + " = null");
			} else {
    			params.add(value);
    			if (count != 0) {
    				updateSql.append(",");
    			}
    			updateSql.append(" " + columnName + " = ?");
			}

			count++;
		}

		updateSql.append(" where ");
		updateSql.append(whereSql);
		params.add(idValue);
		if (versionValue != null) {
			params.add(versionValue);
		}

		SqlParamsPairs sqlAndParams = new SqlParamsPairs(updateSql.toString(), params.toArray());
		logger.debug(sqlAndParams.toString());

		return sqlAndParams;

	}

	/**
	 * 从对象中获取delete语句
	 * 
	 * @param po
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static SqlParamsPairs getDeleteFromObject(Object po) throws Exception {

		// 用来存放insert语句
		StringBuffer deleteSql = new StringBuffer();

		// 用来存储id
		Object idValue = null;

		// 分析表名
		String tableName = getTableName(po.getClass());

		deleteSql.append("delete from " + tableName + " where ");

		Class clazz = po.getClass();
		// 分析列
		Field[] fields = clazz.getDeclaredFields();

		// 用于寻找id字段
		Id idAnno = null;
		for (int i = 0; i < fields.length; i++) {
			Field f = fields[i];

			// 找id字段
			Method getter = getGetter(clazz, f);

			if (getter == null) {
				// 没有get方法直接跳过
				continue;
			}

			// 看是不是主键
			idAnno = getter.getAnnotation(Id.class);
			if (idAnno == null) {
				continue;
			}

			// 看有没有定义column
			String columnName = getColumnNameFromGetter(getter, f);

			deleteSql.append(columnName + " = ?");

			idValue = getter.invoke(po, new Object[] {});

			break;
		}

		// 全部遍历完如果找不到主键就抛异常
		if (idAnno == null) {
			throw new NoIdAnnotationFoundException(clazz);
		}

		SqlParamsPairs sqlAndParams = new SqlParamsPairs(deleteSql.toString(), new Object[] { idValue });
		logger.debug(sqlAndParams.toString());

		return sqlAndParams;

	}

	/**
	 * 获取根据主键查对象的sql和参数
	 * 
	 * @param po
	 * @param id
	 * @return
	 * @throws NoIdAnnotationFoundException
	 * @throws NoColumnAnnotationFoundException
	 * @throws NoDefinedGetterException
	 * @throws @throws
	 *             Exception
	 */
	public static <T> SqlParamsPairs getGetFromObject(Class<T> clazz, Object id)
			throws NoIdAnnotationFoundException, NoColumnAnnotationFoundException {

		// 用来存放get语句
		StringBuffer getSql = new StringBuffer();

		// 分析表名
		String tableName = getTableName(clazz);

		getSql.append("select * from " + tableName + " where ");

		// 分析列
		Field[] fields = clazz.getDeclaredFields();
		Id idAnno = null;
		for (int i = 0; i < fields.length; i++) {
			Field f = fields[i];

			// 找id字段
			Method getter = getGetter(clazz, f);

			if (getter == null) {
				// 没有get方法直接跳过
				continue;
			}

			// 看是不是主键
			idAnno = getter.getAnnotation(Id.class);
			if (idAnno == null) {
				continue;
			}

			// get column name
			String columnName = getColumnNameFromGetter(getter, f);

			getSql.append(columnName + " = ?");

			break;
		}

		// 全部遍历完如果找不到主键就抛异常
		if (idAnno == null) {
			throw new NoIdAnnotationFoundException(clazz);
		}

		SqlParamsPairs sqlAndParams = new SqlParamsPairs(getSql.toString(), new Object[] { id });
		logger.debug(sqlAndParams.toString());

		return sqlAndParams;
	}

	/**
	 * use getter to guess column name, if there is annotation then use
	 * annotation value, if not then guess from field name
	 * 
	 * @param getter
	 * @param clazz
	 * @param f
	 * @return
	 * @throws NoColumnAnnotationFoundException
	 */
	private static String getColumnNameFromGetter(Method getter, Field f) {
		String columnName = "";
		if (!mapMethodColumn.containsKey(getter)) {
			Column columnAnno = getter.getAnnotation(Column.class);
			if (columnAnno != null) {
				// 如果是列注解就读取name属性
				columnName = columnAnno.name();
			}

			if (columnName == null || "".equals(columnName)) {
				// 如果没有列注解就用命名方式去猜
				columnName = CamelNameUtils.camel2underscore(f.getName());
			}
			mapMethodColumn.put(getter, columnName);
		} else {
			columnName = mapMethodColumn.get(getter);
		}
		return columnName;
	}

}
